package com.example.peanutsubway.util;


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {

    // 数据源
    private static DruidDataSource druidDataSource;

    static {
        Properties properties = new Properties();
        // 获取jdbc配置文件
        InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        // 加载配置信息
        try {
            properties.load(inputStream);
            druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = druidDataSource.getConnection();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 执行增删改
     *
     * @param sql
     * @param params
     * @return
     */
    public static int update(String sql, Object... params) {
        int i = 0;
        //获取连接
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            i = queryRunner.update(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return i;
    }

    /**
     * 增删改
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public static int update(Connection connection, String sql, Object... params) {
        int i = 0;
        QueryRunner queryRunner = new QueryRunner();
        try {
            i = queryRunner.update(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }


    /**
     * 新增，返回自增id
     *
     * @param sql
     * @param params
     * @return
     */
    public static Integer insert(String sql, Object... params) {
        Integer id = null;
        //获取连接
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            id = queryRunner.insert(connection, sql, new ScalarHandler<BigInteger>(), params).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return id;
    }

    /**
     * 新增，获取自增的id
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public static BigInteger insert(Connection connection, String sql, Object... params) {
        BigInteger id = null;
        QueryRunner queryRunner = new QueryRunner();
        try {
            id = queryRunner.insert(connection, sql, new ScalarHandler<BigInteger>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    /**
     * 查询所有
     *
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public static <T> List<T> find(String sql, Class clazz, Object... params) {
        List<T> list = new ArrayList<>(10);
        //获取连接
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            list = queryRunner.query(connection, sql, new BeanListHandler<T>(clazz), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return list;
    }

    /**
     * 获取单个实体
     *
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T get(String sql, Class clazz, Object... params) {
        T t = null;
        //获取连接
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            t = queryRunner.query(connection, sql, new BeanHandler<T>(clazz), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return t;
    }

    /**
     * 统计条数
     *
     * @param sql
     * @param params
     * @return
     */
    public static Long count(String sql, Object... params) {
        Long count = 0L;
        //获取连接
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            count = queryRunner.query(connection, sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return count;
    }
}
